﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.Data.Common;
using System.Data.SqlClient;
using System.Data;

namespace ConsoleApplication1
{
    class DS_DB : IDS
    {
        public override IDS IsReady(string Fname)
        {

            path = Fname;
            IDS ret = null;
            string[] strArray = path.Split('.', ' ', '=');
            string extension = strArray[0] + " " + strArray[1];
            if (extension == "Data Source")
            {
                ret = this;
            }
            else
            {
                if (!(next == null)) ret = next.IsReady(Fname);
            }
            return ret;
        }

        public override void Create(MyList xlist)
        {
            DbProviderFactory factory = SqlClientFactory.Instance;
            DbConnection DataBaseconnection = factory.CreateConnection();
            DataBaseconnection.ConnectionString = path;
            DataBaseconnection.Open();

            try
            {
                DbCommand readCommand = factory.CreateCommand();
                readCommand.CommandText = "SELECT * FROM Person";
                readCommand.Connection = DataBaseconnection;
                DbDataReader reader = readCommand.ExecuteReader();
                reader.Close();
            }
            catch (SqlException)
            {
                DbCommand WipeCommand = factory.CreateCommand();
                WipeCommand.CommandText = "CREATE TABLE PERSON (id int,FName char(50), LName char(50),Age int)";
                WipeCommand.Connection = DataBaseconnection;
                WipeCommand.ExecuteScalar();
            }

            DbCommand SaveCommand = factory.CreateCommand();
            SaveCommand.CommandText = "INSERT INTO Person VALUES (@id, @FName, @LName, @Age)";
            SaveCommand.Connection = DataBaseconnection;

            DbParameter Id = factory.CreateParameter();
            Id.ParameterName = "@id";
            SaveCommand.Parameters.Add(Id);

            DbParameter FName = factory.CreateParameter();
            FName.ParameterName = "@FName";
            SaveCommand.Parameters.Add(FName);

            DbParameter LName = factory.CreateParameter();
            LName.ParameterName = "@LName";
            SaveCommand.Parameters.Add(LName);

            DbParameter Age = factory.CreateParameter();
            Age.ParameterName = "@Age";
            SaveCommand.Parameters.Add(Age);

            foreach (Person pp in xlist)
            {
                string[] strArray = pp.SaveToSCV().Split(';');
                Id.Value = strArray[1];
                FName.Value = strArray[2];
                LName.Value = strArray[3];
                Age.Value = strArray[4];
                SaveCommand.ExecuteScalar();
            }

            DataBaseconnection.Close();
        }

        public override MyList Read()
        {
            MyList x = new MyList();

            DbProviderFactory factory = SqlClientFactory.Instance;
            DbConnection DataBaseconnection = factory.CreateConnection();
            DataBaseconnection.ConnectionString = path;
            DataBaseconnection.Open();           
            DbCommand readCommand = factory.CreateCommand();
            readCommand.CommandText = "SELECT * FROM Person";
            readCommand.Connection = DataBaseconnection;
            DbDataReader reader = readCommand.ExecuteReader();
            while (reader.Read())
            {
                object[] values = new object[reader.FieldCount];
                reader.GetValues(values);
                Person type = new Person();
                type.LoadFromDB(values);
                x.Add(type);
            }
            DataBaseconnection.Close();
            return x;
        }

        public override void Update(int id, string fname, string lname, int age)
        {
            DbProviderFactory factory = SqlClientFactory.Instance;
            DbConnection DataBaseconnection = factory.CreateConnection();
            DataBaseconnection.ConnectionString = path;
            DataBaseconnection.Open();

            DbCommand UpdateCommand = factory.CreateCommand();
            UpdateCommand.CommandText = "UPDATE Person SET FName = @FName, LName = @LName, Age = @Age WHERE id = @id";
            UpdateCommand.Connection = DataBaseconnection;

            DbParameter IdParam = factory.CreateParameter();
            IdParam.ParameterName = "@id";
            IdParam.Value = id;
            UpdateCommand.Parameters.Add(IdParam);

            DbParameter FNameParam = factory.CreateParameter();
            FNameParam.ParameterName = "@FName";
            FNameParam.Value = fname;
            UpdateCommand.Parameters.Add(FNameParam);

            DbParameter LNameParam = factory.CreateParameter();
            LNameParam.ParameterName = "@LName";
            LNameParam.Value = lname;
            UpdateCommand.Parameters.Add(LNameParam);

            DbParameter AgeParam = factory.CreateParameter();
            AgeParam.ParameterName = "@Age";
            AgeParam.Value = age;
            UpdateCommand.Parameters.Add(AgeParam);

            UpdateCommand.ExecuteScalar();
            DataBaseconnection.Close();
        }

        public override void Delete(int id)
        {
            DbProviderFactory factory = SqlClientFactory.Instance;
            DbConnection DataBaseconnection = factory.CreateConnection();
            DataBaseconnection.ConnectionString = path;
            DataBaseconnection.Open();
            DbCommand DeleteCommand = factory.CreateCommand();
            DeleteCommand.CommandText = "DELETE FROM Person WHERE id = @id";
            DeleteCommand.Connection = DataBaseconnection;
            DbParameter IdParam = factory.CreateParameter();
            IdParam.ParameterName = "@id";
            IdParam.Value = id;
            DeleteCommand.Parameters.Add(IdParam);
            DeleteCommand.ExecuteScalar();
            DataBaseconnection.Close();
        }
    }
}
